﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using Viettel.VOFFICE.util;

namespace Viettel.VOFFICE.Web.Helpers
{
    public static class QueryHelper
    {
        /// <summary>
        /// Tạo ra câu query lấy văn bản đến, từ thông tin cấu hình
        /// </summary>
        /// <returns></returns>
        public static string MakeDocInboxQuery(out List<TableField> resultFields)
        {
            var uid = UtilityHelper.GetUserId();
            if (null == uid)
            {
                throw new Exception("Cant get current user");
            }
            var selectedFieldFile = HttpContext.Current.Server.MapPath("~/Assets/GridConfig/Inbox/uid" + ".xml");
            if (!new FileInfo(selectedFieldFile).Exists)
            {
                selectedFieldFile = HttpContext.Current.Server.MapPath("~/Assets/GridConfig/Inbox/default.xml");
            }
            if (!new FileInfo(selectedFieldFile).Exists)
            {
                throw new Exception("Cant get current grid config");
            }

            var docInboxGridFile = HttpContext.Current.Server.MapPath("~/Assets/GridConfig/DocInboxGrid.xml");
            if (!new FileInfo(docInboxGridFile).Exists)
            {
                throw new Exception("Cant get current config");
            }

            var docInboxConfig = Utility.DeserializeFileToXml<DocGrid>(docInboxGridFile);
            if (null == docInboxConfig)
            {
                throw new Exception("Config is not good");
            }
            var selectedField = Utility.DeserializeFileToXml<SelectedList>(selectedFieldFile);
            if (null == selectedField)
            {
                throw new Exception("Config for user or default config is not good");
            }
            var selectedFiledIds = selectedField.SelectedFields.Select(o => o.Id).ToList();
            var fields = new List<TableField>();
            foreach (var table in docInboxConfig.TableList.TableItems)
            {
                fields.AddRange(table.TableFieldList.TableFields.Where(o => selectedFiledIds.Contains(o.Id)).Select(o => new TableField()
                   {
                       Id = o.Id,
                       DataType = o.DataType,
                       Join = table.Join,
                       TableAs = table.As,
                       Name = o.Name,
                       FieldAs = o.FieldAs
                   }));
            }
            if (!fields.Any())
            {
                throw new Exception("Config for user or default config is not good");
            }
            //sau đó tạo câu truy vấn
            const string from = " FROM ";
            var query = "SELECT ";
            for (var i = 0; i < fields.Count; i++)
            {
                var tableField = fields[i];
                if (i == 0)
                {
                    query += string.Format(@"{0}.{1} as {2}", tableField.TableAs.Trim(), tableField.Name.Trim(), tableField.FieldAs);
                }
                else
                {
                    query += string.Format(@",{0}.{1} as {2}", tableField.TableAs.Trim(), tableField.Name.Trim(), tableField.FieldAs);
                }
            }
            query += from;
            var joinList = (from p in fields select p.Join).Distinct();
            query = joinList.Aggregate(query, (current, @join) => current + string.Format(@" {0}", @join));

            resultFields = fields;
            return query;
        }

        public static Type GenType(string type)
        {
            switch (type.ToLower())
            {
                case "string":
                    return typeof(String);
                case "date":
                    return typeof(DateTime);
                case "int":
                    return typeof(Int32);
                case "byte":
                    return typeof(Byte);
                case "bit":
                    return typeof(Boolean);
                case "bool":
                    return typeof(Boolean);
                case "short":
                    return typeof(Int16);
                default:
                    return typeof(string);
            }
        }
    }
}